package cn.sixmillions.old;

import cn.sixmillions.domain.Student;
import com.alibaba.fastjson.JSON;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 原始通过jdbc访问数据库
 */
public class AccessDB {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            //驱动
            //mysql5.7
            //Class.forName("com.mysql.jdbc.Driver");
            //mysql8
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接数据库 autoReconnect=true&failOverReadOnly=false&characterEncoding=utf8
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8", "root", "123456");
            //删除demo
            deleteDemo(connection);
            //单条插入demo
            insertDemo(connection);
            //批量插入demo
            insertBatchDemo(connection);
            //查询sql语句demo
            queryDemo(connection);
            //更新demo
            updateDemo(connection);
            //关闭连接
            connection.close();
        } catch (Exception e) {
            System.out.println("异常：" + e.getMessage());
        }
    }

    /**
     * 更新demo
     *
     * @param connection
     */
    private static void updateDemo(Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("update student set student_name = '张三三' where student_id = 111");
        int updateRes = preparedStatement.executeUpdate();
        System.out.println("更新条数：" + updateRes);
        preparedStatement.close();
    }

    /**
     * 删除demo
     *
     * @param connection
     * @throws SQLException
     */
    private static void deleteDemo(Connection connection) throws SQLException {
        String sql = "delete from student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int isDelete = preparedStatement.executeUpdate();
        System.out.println("数据删除条数：" + isDelete);
        preparedStatement.close();
    }

    /**
     * 查询demo
     *
     * @param connection
     * @throws SQLException
     */
    private static void queryDemo(Connection connection) throws SQLException {
        String sql = "select * from student where class_id = ?";
        //预处理statement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //参数
        preparedStatement.setInt(1, 1);
        //执行
        ResultSet resultSet = preparedStatement.executeQuery();
        //遍历运行结果
        List<Student> res = new ArrayList<>();
        while (resultSet.next()) {
            int studentId = resultSet.getInt("student_id");
            String studentName = resultSet.getString("student_name");
            int classId = resultSet.getInt("class_id");
            //封装到student对象
            Student student = new Student();
            student.setStudentId(studentId);
            student.setStudentName(studentName);
            student.setClassId(classId);
            res.add(student);
        }
        //结果
        System.out.println(JSON.toJSONString(res));
        resultSet.close();
        preparedStatement.close();
    }

    /**
     * 单条插入demo
     *
     * @param connection
     * @throws SQLException
     */
    private static void insertDemo(Connection connection) throws SQLException {
        //插入sql语句
        String insertSql = "INSERT INTO student values(?,?,?)";
        //关闭自动提交
        connection.setAutoCommit(false);
        PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
        preparedStatement.setInt(1, 666);
        preparedStatement.setString(2, "sixmillions");
        preparedStatement.setInt(3, 1);
        int insertCnt = preparedStatement.executeUpdate();
        //手动提交
        connection.commit();
        System.out.println("数据插入成功条数：" + insertCnt);
        preparedStatement.close();
    }

    /**
     * 多条插入demo
     *
     * @param connection
     * @throws SQLException
     */
    private static void insertBatchDemo(Connection connection) throws SQLException {
        List<Student> list = new ArrayList<>();
        list.add(new Student(111, "张三", 1));
        list.add(new Student(222, "李四", 2));
        list.add(new Student(333, "王五", 1));
        //插入sql语句
        String insertSql = "INSERT INTO student values(?,?,?)";
        //自动提交
        connection.setAutoCommit(true);
        PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
        for (Student student : list) {
            preparedStatement.setInt(1, student.getStudentId());
            preparedStatement.setString(2, student.getStudentName());
            preparedStatement.setInt(3, student.getClassId());
            preparedStatement.addBatch();
        }
        int[] insertBatchRes = preparedStatement.executeBatch();
        System.out.println("数据是否插入成功：" + JSON.toJSONString(insertBatchRes));
        preparedStatement.close();
    }
}
